<!DOCTYPE html>
<html lang=en>
<head>
    <!-- so meta -->
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="HandheldFriendly" content="True">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1" />
    <meta name="description" content="-- 声明游标；CURSOR cursor_name IS select_statement    --For 循环游标   --（1）定义游标   --（2）定义游标变量   --（3）使用for循环来使用这个游标   declare          --类型定义          cursor c_job          is          select empno,ename,job">
<meta property="og:type" content="article">
<meta property="og:title" content="Oracle游标使用全解-shawnXiao-博客园">
<meta property="og:url" content="http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/index.html">
<meta property="og:site_name" content="TmoonSite">
<meta property="og:description" content="-- 声明游标；CURSOR cursor_name IS select_statement    --For 循环游标   --（1）定义游标   --（2）定义游标变量   --（3）使用for循环来使用这个游标   declare          --类型定义          cursor c_job          is          select empno,ename,job">
<meta property="og:locale" content="en_US">
<meta property="article:published_time" content="2016-02-25T12:19:13.000Z">
<meta property="article:modified_time" content="2020-01-07T10:48:14.914Z">
<meta property="article:author" content="Tmoonlight">
<meta name="twitter:card" content="summary">
    
    
        
          
              <link rel="shortcut icon" href="/images/favicon.ico">
          
        
        
          
            <link rel="icon" type="image/png" href="/images/logo2.gif" sizes="192x192">
          
        
        
          
            <link rel="apple-touch-icon" sizes="180x180" href="/images/logo2.gif">
          
        
    
    <!-- title -->
    <title>Oracle游标使用全解-shawnXiao-博客园</title>
    <!-- styles -->
    
<link rel="stylesheet" href="/css/style.css">

    <!-- persian styles -->
    
      
<link rel="stylesheet" href="/css/rtl.css">

    
    <!-- rss -->
    
    
<meta name="generator" content="Hexo 4.2.0"></head>

<body class="max-width mx-auto px3 ltr">
    
      <div id="header-post">
  <a id="menu-icon" href="#"><i class="fas fa-bars fa-lg"></i></a>
  <a id="menu-icon-tablet" href="#"><i class="fas fa-bars fa-lg"></i></a>
  <a id="top-icon-tablet" href="#" onclick="$('html, body').animate({ scrollTop: 0 }, 'fast');" style="display:none;"><i class="fas fa-chevron-up fa-lg"></i></a>
  <span id="menu">
    <span id="nav">
      <ul>
         
          <li><a href="/">Home</a></li>
         
          <li><a href="/about/">About</a></li>
         
          <li><a href="/archives/">Writing</a></li>
         
          <li><a href="/projects_url">Projects</a></li>
        
      </ul>
    </span>
    <br/>
    <span id="actions">
      <ul>
        
        <li><a class="icon" href="/2016/02/27/%E5%85%A8%E6%96%87%E7%B4%A2%E5%BC%95contains%E9%AB%98%E7%BA%A7%E4%BD%BF%E7%94%A8/"><i class="fas fa-chevron-left" aria-hidden="true" onmouseover="$('#i-prev').toggle();" onmouseout="$('#i-prev').toggle();"></i></a></li>
        
        
        <li><a class="icon" href="/2016/02/18/oracle10g%E5%87%BD%E6%95%B0%E5%A4%A7%E5%85%A8/"><i class="fas fa-chevron-right" aria-hidden="true" onmouseover="$('#i-next').toggle();" onmouseout="$('#i-next').toggle();"></i></a></li>
        
        <li><a class="icon" href="#" onclick="$('html, body').animate({ scrollTop: 0 }, 'fast');"><i class="fas fa-chevron-up" aria-hidden="true" onmouseover="$('#i-top').toggle();" onmouseout="$('#i-top').toggle();"></i></a></li>
        <li><a class="icon" href="#"><i class="fas fa-share-alt" aria-hidden="true" onmouseover="$('#i-share').toggle();" onmouseout="$('#i-share').toggle();" onclick="$('#share').toggle();return false;"></i></a></li>
      </ul>
      <span id="i-prev" class="info" style="display:none;">Previous post</span>
      <span id="i-next" class="info" style="display:none;">Next post</span>
      <span id="i-top" class="info" style="display:none;">Back to top</span>
      <span id="i-share" class="info" style="display:none;">Share post</span>
    </span>
    <br/>
    <div id="share" style="display: none">
      <ul>
  <li><a class="icon" href="http://www.facebook.com/sharer.php?u=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/" target="_blank" rel="noopener"><i class="fab fa-facebook " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://twitter.com/share?url=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&text=Oracle游标使用全解-shawnXiao-博客园" target="_blank" rel="noopener"><i class="fab fa-twitter " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.linkedin.com/shareArticle?url=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&title=Oracle游标使用全解-shawnXiao-博客园" target="_blank" rel="noopener"><i class="fab fa-linkedin " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://pinterest.com/pin/create/bookmarklet/?url=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&is_video=false&description=Oracle游标使用全解-shawnXiao-博客园" target="_blank" rel="noopener"><i class="fab fa-pinterest " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="mailto:?subject=Oracle游标使用全解-shawnXiao-博客园&body=Check out this article: http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/"><i class="fas fa-envelope " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://getpocket.com/save?url=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&title=Oracle游标使用全解-shawnXiao-博客园" target="_blank" rel="noopener"><i class="fab fa-get-pocket " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://reddit.com/submit?url=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&title=Oracle游标使用全解-shawnXiao-博客园" target="_blank" rel="noopener"><i class="fab fa-reddit " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.stumbleupon.com/submit?url=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&title=Oracle游标使用全解-shawnXiao-博客园" target="_blank" rel="noopener"><i class="fab fa-stumbleupon " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://digg.com/submit?url=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&title=Oracle游标使用全解-shawnXiao-博客园" target="_blank" rel="noopener"><i class="fab fa-digg " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.tumblr.com/share/link?url=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&name=Oracle游标使用全解-shawnXiao-博客园&description=" target="_blank" rel="noopener"><i class="fab fa-tumblr " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://news.ycombinator.com/submitlink?u=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&t=Oracle游标使用全解-shawnXiao-博客园" target="_blank" rel="noopener"><i class="fab fa-hacker-news " aria-hidden="true"></i></a></li>
</ul>

    </div>
    <div id="toc">
      
    </div>
  </span>
</div>

    
    <div class="content index py4">
        
        <article class="post" itemscope itemtype="http://schema.org/BlogPosting">
  <header>
    
    <h1 class="posttitle" itemprop="name headline">
        Oracle游标使用全解-shawnXiao-博客园
    </h1>



    <div class="meta">
      <span class="author" itemprop="author" itemscope itemtype="http://schema.org/Person">
        <span itemprop="name">TmoonSite</span>
      </span>
      
    <div class="postdate">
      
        <time datetime="2016-02-25T12:19:13.000Z" itemprop="datePublished">2016-02-25</time>
        
      
    </div>


      

      

    </div>
  </header>
  

  <div class="content" itemprop="articleBody">
    <pre><code>-- 声明游标；CURSOR cursor_name IS select_statement  

--For 循环游标  
--（1）定义游标  
--（2）定义游标变量  
--（3）使用for循环来使用这个游标  
declare  
       --类型定义  
       cursor c_job  
       is  
       select empno,ename,job,sal  
       from emp  
       where job=&apos;MANAGER&apos;;  
       --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型  
       c_row c_job%rowtype;  
begin  
       for c_row in c_job loop  
         dbms_output.put_line(c_row.empno||&apos;-&apos;||c_row.ename||&apos;-&apos;||c_row.job||&apos;-&apos;||c_row.sal);  
       end loop;  
end;  



--Fetch游标  
--使用的时候必须要明确的打开和关闭  

declare   
       --类型定义  
       cursor c_job  
       is  
       select empno,ename,job,sal  
       from emp  
       where job=&apos;MANAGER&apos;;  
       --定义一个游标变量  
       c_row c_job%rowtype;  
begin  
       open c_job;  
         loop  
           --提取一行数据到c_row  
           fetch c_job into c_row;  
           --判读是否提取到值，没取到值就退出  
           --取到值c_job%notfound 是false   
           --取不到值c_job%notfound 是true  
           exit when c_job%notfound;  
            dbms_output.put_line(c_row.empno||&apos;-&apos;||c_row.ename||&apos;-&apos;||c_row.job||&apos;-&apos;||c_row.sal);  
         end loop;  
       --关闭游标  
      close c_job;  
end;  

--1：任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。  
       begin  
         update emp set ENAME=&apos;ALEARK&apos; WHERE EMPNO=7469;  
         if sql%isopen then  
           dbms_output.put_line(&apos;Openging&apos;);  
           else  
             dbms_output.put_line(&apos;closing&apos;);  
             end if;  
          if sql%found then  
            dbms_output.put_line(&apos;游标指向了有效行&apos;);--判断游标是否指向有效行  
            else  
              dbms_output.put_line(&apos;Sorry&apos;);  
              end if;  
              if sql%notfound then  
                dbms_output.put_line(&apos;Also Sorry&apos;);  
                else  
                  dbms_output.put_line(&apos;Haha&apos;);  
                  end if;  
                   dbms_output.put_line(sql%rowcount);  
                   exception   
                     when no_data_found then  
                       dbms_output.put_line(&apos;Sorry No data&apos;);  
                       when too_many_rows then  
                         dbms_output.put_line(&apos;Too Many rows&apos;);  
                         end;  
declare  
       empNumber emp.EMPNO%TYPE;  
       empName emp.ENAME%TYPE;  
       begin  
         if sql%isopen then  
           dbms_output.put_line(&apos;Cursor is opinging&apos;);  
           else  
             dbms_output.put_line(&apos;Cursor is Close&apos;);  
             end if;  
             if sql%notfound then  
               dbms_output.put_line(&apos;No Value&apos;);  
               else  
                 dbms_output.put_line(empNumber);  
                 end if;  
                 dbms_output.put_line(sql%rowcount);  
                 dbms_output.put_line(&apos;-------------&apos;);  

                 select EMPNO,ENAME into  empNumber,empName from emp where EMPNO=7499;  
                 dbms_output.put_line(sql%rowcount);  

                if sql%isopen then  
                dbms_output.put_line(&apos;Cursor is opinging&apos;);  
                else  
                dbms_output.put_line(&apos;Cursor is Closing&apos;);  
                end if;  
                 if sql%notfound then  
                 dbms_output.put_line(&apos;No Value&apos;);  
                 else  
                 dbms_output.put_line(empNumber);  
                 end if;  
                 exception   
                   when no_data_found then  
                     dbms_output.put_line(&apos;No Value&apos;);  
                     when too_many_rows then  
                       dbms_output.put_line(&apos;too many rows&apos;);  
                       end;  



--2,使用游标和loop循环来显示所有部门的名称  
--游标声明  
declare   
       cursor csr_dept  
       is  
       --select语句  
       select DNAME  
       from Depth;  
       --指定行指针,这句话应该是指定和csr_dept行类型相同的变量  
       row_dept csr_dept%rowtype;  
begin  
       --for循环  
       for row_dept in csr_dept loop  
           dbms_output.put_line(&apos;部门名称:&apos;||row_dept.DNAME);  
       end loop;  
end;  


--3,使用游标和while循环来显示所有部门的的地理位置（用%found属性）  
declare  
       --游标声明  
       cursor csr_TestWhile  
       is  
       --select语句  
       select  LOC  
       from Depth;  
       --指定行指针  
       row_loc csr_TestWhile%rowtype;  
begin  
  --打开游标  
       open csr_TestWhile;  
       --给第一行喂数据  
       fetch csr_TestWhile into row_loc;  
       --测试是否有数据，并执行循环  
         while csr_TestWhile%found loop  
           dbms_output.put_line(&apos;部门地点：&apos;||row_loc.LOC);  
           --给下一行喂数据  
           fetch csr_TestWhile into row_loc;  
         end loop;  
       close csr_TestWhile;  
end;   
select * from emp  




--4,接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息(使用循环游标)  
--CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;  
--定义参数的语法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value]    

declare   
      CURSOR   
      c_dept(p_deptNo number)  
      is  
      select * from emp where emp.depno=p_deptNo;  
      r_emp emp%rowtype;  
begin  
        for r_emp in c_dept(20) loop  
            dbms_output.put_line(&apos;员工号：&apos;||r_emp.EMPNO||&apos;员工名：&apos;||r_emp.ENAME||&apos;工资：&apos;||r_emp.SAL);  
        end loop;  
end;  
select * from emp     
--5：向游标传递一个工种，显示此工种的所有雇员的所有信息(使用参数游标)  
declare   
       cursor  
       c_job(p_job nvarchar2)  
       is   
       select * from emp where JOB=p_job;  
       r_job emp%rowtype;  
begin   
       for r_job in c_job(&apos;CLERK&apos;) loop  
           dbms_output.put_line(&apos;员工号&apos;||r_job.EMPNO||&apos; &apos;||&apos;员工姓名&apos;||r_job.ENAME);  
        end loop;  
end;  
SELECT * FROM EMP  

--6：用更新游标来为雇员加佣金：(用if实现,创建一个与emp表一摸一样的emp1表，对emp1表进行修改操作),并将更新前后的数据输出出来   
--http://zheng12tian.iteye.com/blog/815770   
        create table emp1 as select * from emp;  

declare  
        cursor  
        csr_Update  
        is  
        select * from  emp1 for update OF SAL;  
        empInfo csr_Update%rowtype;  
        saleInfo  emp1.SAL%TYPE;  
begin  
    FOR empInfo IN csr_Update LOOP  
      IF empInfo.SAL&lt;1500 THEN  
        saleInfo:=empInfo.SAL*1.2;  
       elsif empInfo.SAL&lt;2000 THEN  
        saleInfo:=empInfo.SAL*1.5;  
        elsif empInfo.SAL&lt;3000 THEN  
        saleInfo:=empInfo.SAL*2;  
      END IF;  
      UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_Update;  
     END LOOP;  
END;  

--7:编写一个PL/SQL程序块，对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水(sal)的10%给他们加薪(对emp1表进行修改操作)  
declare   
     cursor  
      csr_AddSal  
     is  
      select * from emp1 where ENAME LIKE &apos;A%&apos; OR ENAME LIKE &apos;S%&apos; for update OF SAL;  
      r_AddSal csr_AddSal%rowtype;  
      saleInfo  emp1.SAL%TYPE;  
begin  
      for r_AddSal in csr_AddSal loop  
          dbms_output.put_line(r_AddSal.ENAME||&apos;原来的工资:&apos;||r_AddSal.SAL);  
          saleInfo:=r_AddSal.SAL*1.1;  
          UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_AddSal;  
      end loop;  
end;  
--8：编写一个PL/SQL程序块，对所有的salesman增加佣金(comm)500  
declare  
      cursor  
          csr_AddComm(p_job nvarchar2)  
      is  
          select * from emp1 where   JOB=p_job FOR UPDATE OF COMM;  
      r_AddComm  emp1%rowtype;  
      commInfo emp1.comm%type;  
begin  
    for r_AddComm in csr_AddComm(&apos;SALESMAN&apos;) LOOP  
        commInfo:=r_AddComm.COMM+500;  
         UPDATE EMP1 SET COMM=commInfo where CURRENT OF csr_AddComm;  
    END LOOP;  
END;  

--9：编写一个PL/SQL程序块，以提升2个资格最老的职员为MANAGER（工作时间越长，资格越老）  
--（提示：可以定义一个变量作为计数器控制游标只提取两条数据；也可以在声明游标的时候把雇员中资格最老的两个人查出来放到游标中。）  
declare  
    cursor crs_testComput  
    is  
    select * from emp1 order by HIREDATE asc;  
    --计数器  
    top_two number:=2;  
    r_testComput crs_testComput%rowtype;  
begin  
    open crs_testComput;  
       FETCH crs_testComput INTO r_testComput;  
          while top_two&gt;0 loop  
             dbms_output.put_line(&apos;员工姓名：&apos;||r_testComput.ENAME||&apos; 工作时间：&apos;||r_testComput.HIREDATE);  
             --计速器减一  
             top_two:=top_two-1;  
             FETCH crs_testComput INTO r_testComput;  
           end loop;  
     close crs_testComput;  
end;  


--10：编写一个PL/SQL程序块，对所有雇员按他们的基本薪水(sal)的20%为他们加薪，  
--如果增加的薪水大于300就取消加薪(对emp1表进行修改操作，并将更新前后的数据输出出来)   
declare  
    cursor  
        crs_UpadateSal  
    is  
        select * from emp1 for update of SAL;  
        r_UpdateSal crs_UpadateSal%rowtype;  
        salAdd emp1.sal%type;  
        salInfo emp1.sal%type;  
begin  
        for r_UpdateSal in crs_UpadateSal loop  
           salAdd:= r_UpdateSal.SAL*0.2;  
           if salAdd&gt;300 then  
             salInfo:=r_UpdateSal.SAL;  
              dbms_output.put_line(r_UpdateSal.ENAME||&apos;:  加薪失败。&apos;||&apos;薪水维持在：&apos;||r_UpdateSal.SAL);  
             else   
              salInfo:=r_UpdateSal.SAL+salAdd;  
              dbms_output.put_line(r_UpdateSal.ENAME||&apos;:  加薪成功.&apos;||&apos;薪水变为：&apos;||salInfo);  
           end if;  
           update emp1 set SAL=salInfo where current of crs_UpadateSal;  
        end loop;  
end;  

--11:将每位员工工作了多少年零多少月零多少天输出出来     
--近似  
  --CEIL(n)函数：取大于等于数值n的最小整数  
  --FLOOR(n)函数：取小于等于数值n的最大整数  
  --truc的用法 http://publish.it168.com/2005/1028/20051028034101.shtml  
declare  
  cursor  
   crs_WorkDay  
   is  
   select ENAME,HIREDATE, trunc(months_between(sysdate, hiredate) / 12) AS SPANDYEARS,  
       trunc(mod(months_between(sysdate, hiredate), 12)) AS months,  
       trunc(mod(mod(sysdate - hiredate, 365), 12)) as days  
   from emp1;  
  r_WorkDay crs_WorkDay%rowtype;  
begin  
    for   r_WorkDay in crs_WorkDay loop  
    dbms_output.put_line(r_WorkDay.ENAME||&apos;已经工作了&apos;||r_WorkDay.SPANDYEARS||&apos;年,零&apos;||r_WorkDay.months||&apos;月,零&apos;||r_WorkDay.days||&apos;天&apos;);  
    end loop;  
end;  

--12:输入部门编号，按照下列加薪比例执行(用CASE实现，创建一个emp1表，修改emp1表的数据),并将更新前后的数据输出出来  
--  deptno  raise(%)  
--  10      5%  
--  20      10%  
--  30      15%  
--  40      20%  
--  加薪比例以现有的sal为标准  
--CASE expr WHEN comparison_expr THEN return_expr  
--[, WHEN comparison_expr THEN return_expr]... [ELSE else_expr] END  
declare  
     cursor  
         crs_caseTest  
          is  
          select * from emp1 for update of SAL;  
          r_caseTest crs_caseTest%rowtype;  
          salInfo emp1.sal%type;  
     begin  
         for r_caseTest in crs_caseTest loop  
         case   
           when r_caseTest.DEPNO=10  
           THEN salInfo:=r_caseTest.SAL*1.05;  
           when r_caseTest.DEPNO=20  
           THEN salInfo:=r_caseTest.SAL*1.1;  
           when r_caseTest.DEPNO=30  
           THEN salInfo:=r_caseTest.SAL*1.15;  
            when r_caseTest.DEPNO=40  
           THEN salInfo:=r_caseTest.SAL*1.2;  
         end case;  
          update emp1 set SAL=salInfo where current of crs_caseTest;  
        end loop;  
end;  

--13:对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50元，输出更新前后的薪水，员工姓名，所在部门编号。  
--AVG([distinct|all] expr) over (analytic_clause)  
---作用：  
--按照analytic_clause中的规则求分组平均值。  
  --分析函数语法:  
  --FUNCTION_NAME(&lt;argument&gt;,&lt;argument&gt;...)  
  --OVER  
  --(&lt;Partition-Clause&gt;&lt;Order-by-Clause&gt;&lt;Windowing Clause&gt;)  
     --PARTITION子句  
     --按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组  
     select * from emp1  
DECLARE  
     CURSOR   
     crs_testAvg  
     IS  
     select EMPNO,ENAME,JOB,SAL,DEPNO,AVG(SAL) OVER (PARTITION BY DEPNO ) AS DEP_AVG  
     FROM EMP1 for update of SAL;  
     r_testAvg crs_testAvg%rowtype;  
     salInfo emp1.sal%type;  
     begin  
     for r_testAvg in crs_testAvg loop  
     if r_testAvg.SAL&gt;r_testAvg.DEP_AVG then  
     salInfo:=r_testAvg.SAL-50;  
     end if;  
     update emp1 set SAL=salInfo where current of crs_testAvg;  
     end loop;  
end;</code></pre>
  </div>
</article>



        
          <div id="footer-post-container">
  <div id="footer-post">

    <div id="nav-footer" style="display: none">
      <ul>
         
          <li><a href="/">Home</a></li>
         
          <li><a href="/about/">About</a></li>
         
          <li><a href="/archives/">Writing</a></li>
         
          <li><a href="/projects_url">Projects</a></li>
        
      </ul>
    </div>

    <div id="toc-footer" style="display: none">
      
    </div>

    <div id="share-footer" style="display: none">
      <ul>
  <li><a class="icon" href="http://www.facebook.com/sharer.php?u=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/" target="_blank" rel="noopener"><i class="fab fa-facebook fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://twitter.com/share?url=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&text=Oracle游标使用全解-shawnXiao-博客园" target="_blank" rel="noopener"><i class="fab fa-twitter fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.linkedin.com/shareArticle?url=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&title=Oracle游标使用全解-shawnXiao-博客园" target="_blank" rel="noopener"><i class="fab fa-linkedin fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://pinterest.com/pin/create/bookmarklet/?url=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&is_video=false&description=Oracle游标使用全解-shawnXiao-博客园" target="_blank" rel="noopener"><i class="fab fa-pinterest fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="mailto:?subject=Oracle游标使用全解-shawnXiao-博客园&body=Check out this article: http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/"><i class="fas fa-envelope fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://getpocket.com/save?url=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&title=Oracle游标使用全解-shawnXiao-博客园" target="_blank" rel="noopener"><i class="fab fa-get-pocket fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://reddit.com/submit?url=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&title=Oracle游标使用全解-shawnXiao-博客园" target="_blank" rel="noopener"><i class="fab fa-reddit fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.stumbleupon.com/submit?url=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&title=Oracle游标使用全解-shawnXiao-博客园" target="_blank" rel="noopener"><i class="fab fa-stumbleupon fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://digg.com/submit?url=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&title=Oracle游标使用全解-shawnXiao-博客园" target="_blank" rel="noopener"><i class="fab fa-digg fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.tumblr.com/share/link?url=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&name=Oracle游标使用全解-shawnXiao-博客园&description=" target="_blank" rel="noopener"><i class="fab fa-tumblr fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://news.ycombinator.com/submitlink?u=http://yoursite.com/2016/02/25/Oracle%E6%B8%B8%E6%A0%87%E4%BD%BF%E7%94%A8%E5%85%A8%E8%A7%A3-shawnXiao-%E5%8D%9A%E5%AE%A2%E5%9B%AD/&t=Oracle游标使用全解-shawnXiao-博客园" target="_blank" rel="noopener"><i class="fab fa-hacker-news fa-lg" aria-hidden="true"></i></a></li>
</ul>

    </div>

    <div id="actions-footer">
        <a id="menu" class="icon" href="#" onclick="$('#nav-footer').toggle();return false;"><i class="fas fa-bars fa-lg" aria-hidden="true"></i> Menu</a>
        <a id="toc" class="icon" href="#" onclick="$('#toc-footer').toggle();return false;"><i class="fas fa-list fa-lg" aria-hidden="true"></i> TOC</a>
        <a id="share" class="icon" href="#" onclick="$('#share-footer').toggle();return false;"><i class="fas fa-share-alt fa-lg" aria-hidden="true"></i> Share</a>
        <a id="top" style="display:none" class="icon" href="#" onclick="$('html, body').animate({ scrollTop: 0 }, 'fast');"><i class="fas fa-chevron-up fa-lg" aria-hidden="true"></i> Top</a>
    </div>

  </div>
</div>

        
        <footer id="footer">
  <div class="footer-left">
    Copyright &copy; 2020 Tmoonlight
  </div>
  <div class="footer-right">
    <nav>
      <ul>
         
          <li><a href="/">Home</a></li>
         
          <li><a href="/about/">About</a></li>
         
          <li><a href="/archives/">Writing</a></li>
         
          <li><a href="/projects_url">Projects</a></li>
        
      </ul>
    </nav>
  </div>
</footer>

    </div>
    <!-- styles -->

<link rel="stylesheet" href="/lib/font-awesome/css/all.min.css">


<link rel="stylesheet" href="/lib/justified-gallery/css/justifiedGallery.min.css">


    <!-- jquery -->

<script src="/lib/jquery/jquery.min.js"></script>


<script src="/lib/justified-gallery/js/jquery.justifiedGallery.min.js"></script>

<!-- clipboard -->

  
<script src="/lib/clipboard/clipboard.min.js"></script>

  <script type="text/javascript">
  $(function() {
    // copy-btn HTML
    var btn = "<span class=\"btn-copy tooltipped tooltipped-sw\" aria-label=\"Copy to clipboard!\">";
    btn += '<i class="far fa-clone"></i>';
    btn += '</span>'; 
    // mount it!
    $(".highlight table").before(btn);
    var clip = new ClipboardJS('.btn-copy', {
      text: function(trigger) {
        return Array.from(trigger.nextElementSibling.querySelectorAll('.code')).reduce((str,it)=>str+it.innerText+'\n','')
      }
    });
    clip.on('success', function(e) {
      e.trigger.setAttribute('aria-label', "Copied!");
      e.clearSelection();
    })
  })
  </script>


<script src="/js/main.js"></script>

<!-- search -->

<!-- Google Analytics -->

<!-- Baidu Analytics -->

<!-- Disqus Comments -->


</body>
</html>
